{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "9d679d4a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Python3_Jupyter_Nb_SparkSQL_讯捷集团销售数据分析.ipynb\n",
    "# Create By GF 2023-11-27 16:54"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "1f7720a3",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pyspark\n",
    "from pyspark.sql import SparkSession\n",
    "from pyspark.sql.types import IntegerType, DateType\n",
    "from pyspark.sql.window import Window\n",
    "from pyspark.sql.functions import col, desc, format_string, lit, row_number, count, when"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "d2d72477-97b3-45be-85f0-9dfb577714e7",
   "metadata": {},
   "outputs": [],
   "source": [
    "# PySpark 创建 Spark 会话(连接)。\n",
    "# --------------------------------------------------\n",
    "spark = SparkSession.builder.appName('Basic').getOrCreate()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "69332d15-b527-4daa-bccf-c8ba9d39f864",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = spark.read.option(\"header\",\"true\").csv(\"file:///C:\\\\Sales_Data.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "9b118e3d-155f-423f-8b59-093d1834cb8a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['订单号', '日期', '门店名称', '门店等级', '仓库简称', '所属区域', '产品名称', '总类', '单价', '数量', '金额', '成本', '毛利', '预估毛利', '运营代收', '考核毛利', '毛利率', '仓库类别', '体系', '部门', '同比', '商圈', '导购', '套餐标记']\n"
     ]
    }
   ],
   "source": [
    "# 字段列表\n",
    "# --------------------------------------------------\n",
    "print(df.columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "a57880d8-e1ef-418e-a2b0-44ca7f7420b0",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 类型转换\n",
    "ChangeType = df\n",
    "# --------------------------------------------------\n",
    "ChangeType = ChangeType.withColumn(\"日期\",     df[\"日期\"].cast(DateType()))\n",
    "ChangeType = ChangeType.withColumn(\"订单号\",   df[\"订单号\"].cast(IntegerType()))\n",
    "ChangeType = ChangeType.withColumn(\"考核毛利\", df[\"考核毛利\"].cast(\"double\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "f9de2f48-a946-4bc2-9282-8a18fd709551",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- 订单号: integer (nullable = true)\n",
      " |-- 日期: date (nullable = true)\n",
      " |-- 门店名称: string (nullable = true)\n",
      " |-- 门店等级: string (nullable = true)\n",
      " |-- 仓库简称: string (nullable = true)\n",
      " |-- 所属区域: string (nullable = true)\n",
      " |-- 产品名称: string (nullable = true)\n",
      " |-- 总类: string (nullable = true)\n",
      " |-- 单价: string (nullable = true)\n",
      " |-- 数量: string (nullable = true)\n",
      " |-- 金额: string (nullable = true)\n",
      " |-- 成本: string (nullable = true)\n",
      " |-- 毛利: string (nullable = true)\n",
      " |-- 预估毛利: string (nullable = true)\n",
      " |-- 运营代收: string (nullable = true)\n",
      " |-- 考核毛利: double (nullable = true)\n",
      " |-- 毛利率: string (nullable = true)\n",
      " |-- 仓库类别: string (nullable = true)\n",
      " |-- 体系: string (nullable = true)\n",
      " |-- 部门: string (nullable = true)\n",
      " |-- 同比: string (nullable = true)\n",
      " |-- 商圈: string (nullable = true)\n",
      " |-- 导购: string (nullable = true)\n",
      " |-- 套餐标记: string (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# PySpark 打印 DataFrame 的每个字段(列)的类型。\n",
    "# --------------------------------------------------\n",
    "ChangeType.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "b47aa60c-5210-4a4c-8291-74837f818fed",
   "metadata": {},
   "outputs": [],
   "source": [
    "# PySpark 使用 row_number() 函数获取行号。\n",
    "ChangeType = ChangeType.withColumn(\"linenum\", row_number().over(Window.orderBy(\"日期\")))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "3f53a0d0-5d78-4cd2-8ee3-24ad64f64c28",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------+--------+----------+----+----+-----------------------+----+----+------+--------+-------------+\n",
      "|linenum|  订单号|      日期|体系|部门|               产品名称|单价|数量|  金额|考核毛利|         导购|\n",
      "+-------+--------+----------+----+----+-----------------------+----+----+------+--------+-------------+\n",
      "|      1|22338496|2023-08-01|华为|西安| 荣耀90-AN00(16G+512...|   0|   1|2881.0|  451.86|何养婷(34943)|\n",
      "|      2|22338513|2023-08-01|形象|贵州|               立本回收|   0|   1|   0.0|     0.0|杨冬旭(73217)|\n",
      "|      3|22338536|2023-08-01|形象|四部|   苹果iphone13(256G)粉|   0|   1|5499.0|   148.5|吴小渝(13201)|\n",
      "|      4|22338550|2023-08-01|形象|四部|    Apple系统终身服务卡|   0|   1|1288.0|  1287.2|曾成富(65559)|\n",
      "|      5|22338550|2023-08-01|形象|四部|            两年碎屏PRO|   0|   1| 228.0|    38.0|曾成富(65559)|\n",
      "|      6|22338550|2023-08-01|形象|四部|               验机服务|   0|   1|   0.0|     0.0|曾成富(65559)|\n",
      "|      7|22339157|2023-08-01|形象|西安|               技术服务|   0|   1| 337.0|   337.0|董运超(06996)|\n",
      "|      8|22339158|2023-08-01|形象|一部|               保障服务|   0|   0| 203.0|   203.0|王乙岚(9172X)|\n",
      "|      9|22339159|2023-08-01|形象|一部|iKeloah镜头膜iP14Pro...|   0|   1| 136.0|   120.6|  梁超(00714)|\n",
      "|     10|22339170|2023-08-01|形象|云南|   Mophie PD 30W USB...|   0|   1| 158.0|  113.96|晏德芬(51728)|\n",
      "|     11|22339190|2023-08-01|形象|二部|  iKeloah膜iP14ProMa...|   0|   1| 190.0|   160.5|  胡旭(77228)|\n",
      "|     12|22339209|2023-08-01|形象|西安|             活动优惠券|   0|   0|-100.0|  -100.0|  赵鑫(06412)|\n",
      "|     13|22339225|2023-08-01|形象|三部|               保障服务|   0|   0| 446.0|   446.0|欧榕海(8427X)|\n",
      "|     14|22339259|2023-08-01|形象|重庆|         达尔文保障计划|   0|   1| 378.0|    63.0|周玉莲(87188)|\n",
      "|     15|22339398|2023-08-01|形象|贵州|               保障服务|   0|   0|1154.0|  1154.0|  罗丹(90029)|\n",
      "|     16|22339443|2023-08-01|形象|西安|   USB-C to Lightnin...|   0|   1| 145.0|   107.0|董运超(06996)|\n",
      "|     17|22339468|2023-08-01|形象|贵州|  RideAir膜iP14ProMa...|   0|   1| 240.0|   207.0|  云莉(14022)|\n",
      "|     18|22339468|2023-08-01|形象|贵州| 碎屏保障服务两年期PG-L|   0|   1| 126.0|    21.0|  云莉(14022)|\n",
      "|     19|22339501|2023-08-01|华为|贵州|   真空保温杯（CX礼品）|   0|  -1|   0.0|     0.0|张筱沐(18023)|\n",
      "|     20|22339510|2023-08-01|形象|重庆|   Mophie PD 30W USB...|   0|   1| 168.0|   123.8|  叶鹏(28916)|\n",
      "+-------+--------+----------+----+----+-----------------------+----+----+------+--------+-------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# 筛选字段\n",
    "FilterField = ChangeType\n",
    "# --------------------------------------------------\n",
    "FilterField.select([\"linenum\", \"订单号\", \"日期\", \"体系\", \"部门\", \"产品名称\", \"单价\", \"数量\", \"金额\", \"考核毛利\", \"导购\"]).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "393dadf2-b524-4075-992a-70d9703182b8",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------+----------+------------------------------+-----------------+\n",
      "|  订单号|      日期|                      产品名称|        Pack_Mark|\n",
      "+--------+----------+------------------------------+-----------------+\n",
      "|22338610|2023-08-01|          USB-C to Lightnin...|           Single|\n",
      "|22338929|2023-08-01|          iKeloah USB-C to ...|Package(22338929)|\n",
      "|22338929|2023-08-01|          Mophie PD 30W USB...|Package(22338929)|\n",
      "|22340298|2023-08-01|                      立本回收|           Single|\n",
      "|22340843|2023-08-01|          苹果iphone14(128G)紫|Package(22340843)|\n",
      "|22340843|2023-08-01|     RideAir膜iP14康宁盔甲W...|Package(22340843)|\n",
      "|22340843|2023-08-01|          Mophie PD 30W USB...|Package(22340843)|\n",
      "|22340843|2023-08-01|                      验机服务|Package(22340843)|\n",
      "|22340843|2023-08-01|    TredPro苹果手机壳iP14机...|Package(22340843)|\n",
      "|22341078|2023-08-01|          Mophie PD 30W USB...|Package(22341078)|\n",
      "|22341078|2023-08-01|Nuguma 透明超级快充移动电源...|Package(22341078)|\n",
      "|22341419|2023-08-01|     RideAir苹果手机壳iP14P...|Package(22341419)|\n",
      "|22341419|2023-08-01|                      技术服务|Package(22341419)|\n",
      "|22341419|2023-08-01|        苹果iphone14pro(256...|Package(22341419)|\n",
      "|22341419|2023-08-01|     苹果原装触控笔二代(A2051)|Package(22341419)|\n",
      "|22341419|2023-08-01|        苹果(M2)iPad-pro11....|Package(22341419)|\n",
      "|22342194|2023-08-01|      苹果纪念保温杯（CX礼品）|Package(22342194)|\n",
      "|22342194|2023-08-01|       iKeloah镜头膜iP14Pro...|Package(22342194)|\n",
      "|22342194|2023-08-01|                      技术服务|Package(22342194)|\n",
      "|22342194|2023-08-01|        苹果iphone14promax(...|Package(22342194)|\n",
      "+--------+----------+------------------------------+-----------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# 计算字段: 套餐标记\n",
    "Calculate_1 = FilterField\n",
    "# --------------------------------------------------\n",
    "Calculate_1 = Calculate_1.withColumn(\"Pack_Mark\", count(\"订单号\").over(Window.partitionBy(\"订单号\")))\n",
    "# --------------------------------------------------\n",
    "# 使用 withColumn 方法来创建一个新的 DataFrame, 并将 \"Pack_Mark\" 的值修改为 \"Package, 仅当条件满足时, 否则保持原来的值。\n",
    "Calculate_1 = Calculate_1.withColumn(\"Pack_Mark\", when(col(\"Pack_Mark\") >= 2, \"Package\").otherwise(col(\"Pack_Mark\")))\n",
    "Calculate_1 = Calculate_1.withColumn(\"Pack_Mark\", when(col(\"Pack_Mark\") == \"Package\", format_string(\"%s(%d)\", col(\"Pack_Mark\"), col(\"订单号\")))\n",
    "                                                 .otherwise(\"Single\"))\n",
    "# --------------------------------------------------\n",
    "Calculate_1.select([\"订单号\", \"日期\", \"产品名称\", \"Pack_Mark\"]).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "879f61b1",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------------+-----------------+--------+\n",
      "|         导购|        Pack_Mark|Sum_Prof|\n",
      "+-------------+-----------------+--------+\n",
      "|  雷汉(61013)|           Single|28126.04|\n",
      "|刘畅昌(30511)|           Single|26605.19|\n",
      "|欧菲菲(80423)|Package(22537275)|  3979.5|\n",
      "|  李钊(50918)|           Single| 11051.5|\n",
      "|杨瑞环(00543)|Package(22686195)|   11.36|\n",
      "|  王磊(82033)|           Single|13178.89|\n",
      "|俞天裕(75535)|           Single|25983.52|\n",
      "|  赵宇(55325)|           Single|36952.26|\n",
      "|刘静文(36625)|           Single|18830.92|\n",
      "|  李秀(30045)|           Single|23844.05|\n",
      "|  刘鸣(76311)|Package(22357766)|   573.9|\n",
      "|陈小英(14426)|           Single|36121.85|\n",
      "|  罗爽(20027)|Package(23033504)|  1042.0|\n",
      "|  申斌(30017)|Package(22434726)|  780.66|\n",
      "|杨瑞环(00543)|Package(22838431)|   528.2|\n",
      "|刘海燕(76443)|Package(22898717)| 1488.85|\n",
      "|吴吉西(8004X)|           Single|17871.36|\n",
      "|  熊磊(26218)|Package(22408347)| 1231.48|\n",
      "|薛婷婷(19167)|Package(22678869)|  5320.2|\n",
      "|  王鑫(20534)|Package(22967900)|  -835.0|\n",
      "+-------------+-----------------+--------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# 计算字段: 毛利相关 - Step 1\n",
    "Part_2 = Calculate_1\n",
    "# --------------------------------------------------\n",
    "Part_2 = Part_2.select([\"导购\", \"Pack_Mark\", \"考核毛利\"]).groupBy([\"导购\", \"Pack_Mark\"]).sum(\"考核毛利\")\n",
    "# --------------------------------------------------\n",
    "Part_2 = Part_2.withColumnRenamed(\"sum(考核毛利)\", \"Sum_Prof\")\n",
    "# --------------------------------------------------\n",
    "Part_2 = Part_2.withColumn(\"Sum_Prof\", pyspark.sql.functions.round(col(\"Sum_Prof\"), 2))\n",
    "# --------------------------------------------------\n",
    "Part_2.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "86aaa4d1",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------------+-----------------+--------+---------+---------+\n",
      "|         导购|        Pack_Mark|Sum_Prof|Feat_Prof|High_Prof|\n",
      "+-------------+-----------------+--------+---------+---------+\n",
      "|  雷汉(61013)|           Single|28126.04|  28000.0|        1|\n",
      "|刘畅昌(30511)|           Single|26605.19|  26500.0|        1|\n",
      "|欧菲菲(80423)|Package(22537275)|  3979.5|   3500.0|        0|\n",
      "|  李钊(50918)|           Single| 11051.5|  11000.0|        1|\n",
      "|杨瑞环(00543)|Package(22686195)|   11.36|      0.0|        0|\n",
      "|  王磊(82033)|           Single|13178.89|  13000.0|        1|\n",
      "|俞天裕(75535)|           Single|25983.52|  25500.0|        1|\n",
      "|  赵宇(55325)|           Single|36952.26|  36500.0|        1|\n",
      "|刘静文(36625)|           Single|18830.92|  18500.0|        1|\n",
      "|  李秀(30045)|           Single|23844.05|  23500.0|        1|\n",
      "|  刘鸣(76311)|Package(22357766)|   573.9|    500.0|        0|\n",
      "|陈小英(14426)|           Single|36121.85|  36000.0|        1|\n",
      "|  罗爽(20027)|Package(23033504)|  1042.0|   1000.0|        0|\n",
      "|  申斌(30017)|Package(22434726)|  780.66|    500.0|        0|\n",
      "|杨瑞环(00543)|Package(22838431)|   528.2|    500.0|        0|\n",
      "|刘海燕(76443)|Package(22898717)| 1488.85|   1000.0|        0|\n",
      "|吴吉西(8004X)|           Single|17871.36|  17500.0|        1|\n",
      "|  熊磊(26218)|Package(22408347)| 1231.48|   1000.0|        0|\n",
      "|薛婷婷(19167)|Package(22678869)|  5320.2|   5000.0|        1|\n",
      "|  王鑫(20534)|Package(22967900)|  -835.0|   -500.0|        0|\n",
      "+-------------+-----------------+--------+---------+---------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# 计算字段: 毛利相关 - Step 2\n",
    "# --------------------------------------------------\n",
    "Part_2 = Part_2.withColumn(\"Feat_Prof\", col(\"Sum_Prof\") - (col(\"Sum_Prof\") % 500))\n",
    "Part_2 = Part_2.withColumn(\"High_Prof\", when(col(\"Sum_Prof\") >=  5000,  1)\n",
    "                                       .when(col(\"Sum_Prof\") <= -5000, -1)\n",
    "                                       .otherwise(0))\n",
    "# --------------------------------------------------\n",
    "Part_2.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5a4af15d",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
